다. 배치형 개발

• 가장 흔한 형태가 INSERT를 해야 할지 UPDATE를 해야 할지 판단이 서지 않을 때 UPDATE를 먼저 시도한 후 실패하면 INSERT를 시도한다.
• 최악의 경우 SQL을 항상 2번 수행시켜야 하는 비효율이 있다. Oracle 9i 이상에서는 Merge문을 이용하여 한번에 수행할 수 있다.
  • Merge문

    MARGE INTO target_name
        USING (table|view|subquery) ON (join condition)
    WHEN MATCHED THEN
        UPDATE SET col1 = val1[,col2=val2]
    WHEN NOT MATCHED THEN
        INSER(....) VALUES(....)
    
    - INTO : DATA가 UPDATE 되거나 INSERT될 TABLE 이름을 지정합니다.
    - USING : 대상 TABLE의 DATA와 비교한 후  UPDATE 또는 INSERT할 대상이 되는 DATA의 SOURCE 테이블 또는 뷰를 지정
    - ON : UPDATE나 INSERT를 하게 될 조건으로, 해당 condition을 만족하는 DATA가 있으면 WHEN MATCHED절을 시행하게 되고 없으면  WHEN NOT MATCHED 을 실행하게 됩니다.
    - WHEN MATCHED : ON 조건절이 TRUE인 ROW에 수행할 내용
    - WHEN NOT MATCHED : ON 조건절에 맞는 ROW가 없을 때 수행할 내용.
    

1. INSERT가 많은 경우의 배치처리

   UPDATE 할 레코드만 추출하여 임시 TABLE에 보관 ARRAY PROCESSING으로 INSERT 임시 TABLE과 처리 TABLE을 JOIN ARRAY PROCESSING으로 UPDATE 처리(ROWID) 임시 TABLE DROP

1. SQL을 이용한 배치처리

   하나의 SQL로 처리되기 때문에 전체 작업이 완료되었을 때만 커밋이나 롤백을 할 수 있다. 그러므로 너무 많은 로우를 처리할 때는 사용하지 않는 것이 좋다. : sub query절에 해당하는 Temp Table을 만들어 다중 처리를 고려한다. 서브쿼리 조건을 만족하는 로우가 하나도 없어 'No Data Found'가 되었을 때는 SET 절에 기술한 UPDATE 컬럼들에 NULL값이 UPDATE 된다. NVL() EXSITS Updatable Join View 참고) 집계 함수는 Return되는 행이 없더라도 NULL 값으로 Row를 반환한다. 단 COUNT()는 예외

JDBC 배치실행

• JDBC 프로그래밍을 개선하는 원칙은 다음 두가지로 요약할 수 있다.
  • 가능한 덜 수행하라
  • 가능한 한꺼번에 수행하라

• 가능한 덜 수행하라
  : 커서를 재활용하라. 이것은 일단 생성(오픈)한 커스를 닫지 않고 최대한 여러번 활용한다는 의미이다.

  Case1: Statement를 재활용하지 않는 경우
  startTime = System.currentTimeMillis();
  for(int idx=1;idx<=10000; idx++){
      pstmt = conn.prepareStatement("SELECT /* NO REUSER */ 1 FROM java_test WHERE ROWNUM = ?");
      pstmt.setInt(1,1);
      ResultSet rs = pstmt.executeQuery();
      pstmt.close(); //커서를 즉시 닫는다.
  }
  endTime = System.currentTimeMillis();
  System.out.println("Case1: "+ (endTime  - startTime )/10 + " (cs) elapsed");
  
  
  Case2: Statement를 재활용하는 경우
  startTime = System.currentTimeMillis();
  for(int idx=1;idx<=10000; idx++){
      pstmt = conn.prepareStatement("SELECT /* NO REUSER */ 1 FROM java_test WHERE ROWNUM = ?");
      pstmt.setInt(1,1);
      ResultSet rs = pstmt.executeQuery();
      //커서를 닫지 않고 재활용한다.
  }
  pstmt.close();
  endTime = System.currentTimeMillis();
  System.out.println("Case2: "+ (endTime  - startTime )/10 + " (cs) elapsed");
  
  //------ output
  Case1: 7136 (cs) elapsed
  Case2: 1015 (cs) elapsed
  

• 가능한 한꺼번에 수행하라
  : JDBC에서는 pREPAREDsTATEMENT 객체의 addBatch, executeBatch 메소드를 이용해서 배치 실행을 구현할 수 있다.
  : 주의사항
  • 배치작업 요청을 한꺼번에 수행하기 때문에 부가적인 자원(Resource)을 필요로 한다. 즉 1000개의 요청을 한번에 받아서 처리한다면 1개씩 처리하는 경우에 비해 1000배의 자원을 요구한다. 따라서 시스템 자원환경을 고려하여 필요한 모듈을 선별적으로 사용하도록 한다.

    Case1: Batched Execution을 사용하지 않는 경우
    startTime = System.currentTimeMillis();
    pstmt = conn.prepareStatement("UPDATE/* NO BATCH*/ java_test SET id=id WHERE ROWNUM = ?");
    for(int idx=1;idx<=10000; idx++){
        pstmt.setInt(1,1);
        pstmt.executeUpdate(); //매번 수행한다.
    }
    pstmt.close();
    endTime = System.currentTimeMillis();
    
    System.out.println("Case1: "+ (endTime  - startTime )/10 + " (cs) elapsed");
    
    Case2: Batched Execution을 사용하는 경우
    startTime = System.currentTimeMillis();
    pstmt = conn.prepareStatement("UPDATE/* NO BATCH*/ java_test SET id=id WHERE ROWNUM = ?");
    for(int idx=1;idx<=10000; idx++){
        pstmt.setInt(1,1);
        pstmt.addBatch(); //매번 수행하는 대신 ddBatch를 이용해 요청을 모은다.
    }
    pstmt.executeBatch(); //addBatch를 통해 모아둔 요청을 한꺼번에 수행한다.
    endTime = System.currentTimeMillis();
    
    System.out.println("Case2: "+ (endTime  - startTime )/10 + " (cs) elapsed");
    
    
    //------ output
    Case1: 1839 (cs) elapsed
    Case2: 270 (cs) elapsed
    

• 페치크기
  : 커서를 통해 추출(fatch)하는 데이터의 양이 많은 어플리케이션의 경우, 페치 크기를 크게 함으로써 상당한 성능개선 효과를 얻을 수 있다.

  Case1: Fatch size를 10으로 설정한 경우
  startTime = System.currentTimeMillis();
  pstmt = conn.prepareStatement("SELECT /* SMALL FETCH SIZE */ 1 FROM java_test");
  pstmt.setFatchSize(10); //작은 패치 크기
  for(int idx=1;idx<=10000; idx++){
      ResultSet rs = pstmt.executeQuery();
      while(rs.next()) {} //모든 로우를 패치
  }
  pstmt.close();
  endTime = System.currentTimeMillis();
  
  System.out.println("Case1: "+ (endTime  - startTime )/10 + " (cs) elapsed");
  
  Case2: Fatch size를 100으로 설정한 경우
  startTime = System.currentTimeMillis();
  pstmt = conn.prepareStatement("SELECT /* SMALL FETCH SIZE */ 1 FROM java_test");
  pstmt.setFatchSize(10); //큰 패치 크기
  for(int idx=1;idx<=10000; idx++){
      ResultSet rs = pstmt.executeQuery();
      while(rs.next()) {} //모든 로우를 패치
  }
  pstmt.close();
  endTime = System.currentTimeMillis();
  
  System.out.println("Case2: "+ (endTime  - startTime )/10 + " (cs) elapsed");
  
  //------ output
  Case1: 1509 (cs) elapsed
  Case2: 396 (cs) elapsed
  

SQL Trace결과를 비교해보면, 성능 차이를 결정짓는 요소는 페치(Fetch) 횟수라는 것을 알 수 있다. 페치 크기가 10인 경우에는 페치 횟수가 15,000번에 달하는 반면, 100인 경우는 2,000에 불과하다.
더 재미있는 사실은 Logical Reads(query)페치 크기가 줄어든다는 사실이다!!! (각자 trace떠보셈)
그렇지만 이것 또한 부적절하게 사용하면 자원의 경합을 유발할 수 있기 때문에 대량의 추출하는 특정 모듈에서만 큰 페치 크기를 적용하라.

FATCH 와 SELECT INTO 차이점

라. 병렬작업

• 병렬쿼리 - 단일 sql문을 서로 다른 프로세스/쓰레드에 의해 동시에 수행될 수 있는 일련의 작업으로 분할하여 이들을 동시에 실행시킬 수 있는 능력
• 병렬 DML(수정) - 병렬 쿼리와 유사하지만 INSERT, UPDATE, DELETE문에 적용됨
• 병렬 DDL - 15개의 CREATE INDEX 문을 동시에 실행 시키는 대신 N개의 프로세스를 사용하여 병렬로 단일 인덱스를 생성하는 CREATE INDEX와 같은 다양한 문을 병렬로 실행시킬 수 있는 관리자용 능력
• 병렬 DIY - 자기 스스로 하는 병렬 처리로서 실제로 전통적인 오라클 병렬처리 기법은 아니다. 오라클이 작업을 분할하지 않고 사용자가 분할한다.

병렬 처리는 보약이 될 수 있다. 큰 문제를 여러 개의 보다 작은 문제로 나누면(분할 접근법) 처리 시간을 획기적으로 줄일 수 있다. 그러나 병렬 처리는 독이 될 수도 있다. 병렬 처리를 많은 부류의 문제에 적용하면 이들의 속도를 저하시킬 뿐만 아니라 엄청난 리소스를 소비한다.

병렬 쿼리의 시작비용은 매우 높기 때문에 대상 쿼리가 실제로는 느리게 시작된다.

병렬관리

• 병렬관리를 db에 맡겨라..

  create table T nologging parallel as select * from external_table

  여기서 병렬 처리의 정도는 이용 가능한 리소스에 근거하여 오라클 자체에 의해 선택 될 수 있다.
  대용량 데이터에 대한 DDL 수행시 Parallel 옵션을 적절히 사용하면 DDL 자체의 성능을 극대화할 수 있다. 더불어 Nologging 옵션을 함께 사용해도 좋다. DDL의 수행속도가 향상되면, 그에 비례해서 TX락 경합에 의한 대기시간도 줄어들게 된다.

병렬 쿼리

병렬 쿼리는 성능문제를 해결하면서 시도하는 마지막 수단이지 결코 첫 번째 경로는 아니다.
병렬 쿼리는 동시 사용자의 수가 낮은 데이터 웨어하우스에 유용한 기능이다. CPU가 64개이고 동시사용자 수가 500인 데이터 웨어하우스에는 병렬 쿼리가 적합하지 않을 것이다. 그러나 CPU가 64개이고 동시 사용자 수가 5인 데이터 웨어하우스에는 병렬 쿼리가 훌륭한 솔루션이 될 수 있다.

• tomas kyte의 병렬쿼리에 대한 충고
  1. 관련된 테이블을 PARALLEL로 설정하고 병렬 처리의 정도를 지정하지 마라.
  2. 병렬 자동 튜닝을 사용하라. - PARALLEL_AUTOMATIC_TUNING = TRUE(병렬 처리정도가 시스템 부하에 따라 자동으로 결정되고 변경된다)
  3. 병렬 쿼리를 사용하지 않아도 되는 방도를 찾아라.

병렬 DML

병렬 DML(PDML)은 실제로 CREATE TABLE, CREATE INDEX, 또는 적재와 유사한 관리 기능으로서 어쩌다 레거시 데이터를 고치기 위하여 한 번에 대용량 UPDATE 또는 DELETE를 수행하기 위하여 사용된다.

• 제약사항
  1. 수정되고 있는 테이블에 트리거가 있어서는 안된다.
  2. PDML은 복제될 수 없다.(왜냐하면, 이작은 트리거를 통해 수행되기 때문이다.)
  3. 지원 가능한 무결성 제약 조건의 유형에는 제한적이다. - 테이블이 다수의 세션에 의해 병렬로 수정되고 있고 한 세션이 다른 세션의 변경 내용을 볼 수 없기 때문
  4. PDML에 데이터베이스 링크(분산 트랜ㄴ잭션)을 사용할 수 없다.
  5. 클러스터 테이블(B*트리 클러스터와 해시 클러스터)은 지원되지 않는다.

위의 제약사항을 하나라도 위반하면 PDML작업을 시도하면 오류 메시지를 받지는 않지만 문이 직렬로 수행된다. V$PX_PROCESS와 같은 동적 성능 뷰를 이용하면 문의 병렬 처리를 모니터링할 수 있다.